Configuring cascading value lists

Cascading value lists are configured by writing a VBScript function for each property that will retrieve only the values related to the value already selected for a different property. The functions are based on the TeamWork GetValues methods, which are further described in the BlueCielo TeamWork VBScript API Reference. The functions are typed as a validation rule for each property.

To configure cascading value lists:

1. In Configurator, on the Edit menu, select Edit Events. The TeamWork Script Editor appears, showing the vault’s existing VBScript event procedures, functions, and other code. For information on customizing the TeamWork event procedures, see the BlueCielo TeamWork VBScript API Reference.
2. Scroll to the bottom of the existing code, and create a new function similar to the following code to present a list of values for the first (top) level property (Name) of the cascading value lists:

Function GetClients GetClients = Vault.Table ("Clients").GetValues (Null, Null, "Name", True, "Name")
End Function

This function retrieves all values from the Name column of the Clients table.

3. Click OK to save your changes.
4. Select This expression and type GetClients on the Validation page of the ClientName property.
5. On a new empty line in the TeamWork Script Editor, create a new function similar to the following code to present a list of values for the next lower (second) level property (ProjectNumber) of the cascading value lists:

Function GetClientProjects GetClientProjects = Vault.Table ("Clients").GetValues ("Name", Document.ClientName, "Project", True, "Project")
End Function

This function retrieves only values from the Project column of the Clients table based on the value selected for Document.ClientName at that moment.

6. Click OK to save your changes.
7. Select This expression and type GetClientProjects on the Validation page of the ProjectNumber property.
8. On a new empty line in the TeamWork Script Editor, create a new function similar to the following code to present a list of values for the next lower (third) level property (ProjectNumber) of the cascading value lists:

Function GetClientProjectContracts GetClientProjectContracts = Vault.Table ("Clients").GetValues (Array ("Name", "Project"), Array (Document.ClientName, Document.ProjectNumber), "Contracts", True, "Contracts")
End Function

This function retrieves only values from the Contracts column of the Clients table based on the value selected for Document.ClientName and Document.ProjectNumber at that moment.

9. Click OK to save your changes.
10. Select This expression and type GetClientProjectContracts on the Validation page of the ContractNumber property.
11. On a new empty line in the TeamWork Script Editor, create a new function similar to the following code to present a list of values for the next lower (fourth and final) level property (ContactName) of the cascading value lists:

Function GetClientProjectContractContacts GetClientProjectContractContacts = Vault.Table ("Clients").GetValues (Array ("Name", "Project", "Contract"), Array (Document.ClientName, Document.ProjectNumber, Document.ContractNumber), "ContactName", True, "ContactName")
End Function

This function retrieves only values from the ContractName column of the Clients table based on the value selected for Document.ClientName, Document.ProjectNumber, and Document.ContractNumber at that moment.

12. Click OK to save your changes.
13. Select This expression and type GetClientProjectContractContacts on the Validation page of the ContactNames property.

In all of the preceding example functions, the fourth argument of the GetValues method is True. This is to ensure that the [Distinct] SQL query argument is also True, which ignores duplicate values.

Note     When using column names that contain spaces, you must enclose the column name within square brackets, for example: "[Column name with spaces]".